package com.example.service.impl;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.write.metadata.fill.FillWrapper;
import com.example.DTO.TablesInfoDTO;
import com.example.domain.Columns;
import com.example.domain.TableConstraintsAndColumn;
import com.example.mapper.ColumnsMapper;
import com.example.mapper.TableConstraintsMapper;
import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import javax.swing.text.TabStop;

import com.example.domain.Tables;
import com.example.mapper.TablesMapper;
import com.example.service.TablesService;

import java.io.*;
import java.util.ArrayList;
import java.util.List;

@Service
public class TablesServiceImpl implements TablesService {

    @Resource
    private TablesMapper tablesMapper;
    @Resource
    private ColumnsMapper columnsMapper;
    @Resource
    private TableConstraintsMapper tableConstraintsMapper;

    @Override
    public int insert(Tables record) {
        return tablesMapper.insert(record);
    }

    @Override
    public int insertSelective(Tables record) {
        return tablesMapper.insertSelective(record);
    }

    @Override
    public TablesInfoDTO selectBySchemaDTO(String tableSchema) {
        TablesInfoDTO tablesInfoDTO = new TablesInfoDTO();
        // 获取表名， 表注释
        List<Tables> tables = tablesMapper.selectBySchema(tableSchema);
        List<String> tableName = new ArrayList<>();
        List<String> tableComment = new ArrayList<>();

        tables.forEach(table -> {
            tableName.add(table.getTableName());
            tableComment.add(table.getTableComment());
        });

        tablesInfoDTO.setTableName(tableName);
        tablesInfoDTO.setTableComment(tableComment);

        return tablesInfoDTO;
    }

    @Override
    public List<Tables> selectBySchema(String tableSchema) {
        return tablesMapper.selectBySchema(tableSchema);
    }

    @Override
    public int generateTablesStructure() throws IOException {
        // 获得所有表名的集合
        List<Tables> tables = tablesMapper.selectBySchema("shop");

        InputStream template = createTemplate(tables);
        int isSuc = fillData(tables, template);

        return isSuc;
    }

    /**
     * 使用模板填充数据
     * @param tables  所有表名的集合
     * @param template 填充模板的输入流
     * @return 1 -- 成功
     */
    private int fillData(List<Tables> tables, InputStream template) {
        int sheetNo = tables.size() + 1;
        String tableName = null;

        // 输出文件路径
        String fileName = "D:\\target" + System.currentTimeMillis() + ".xlsx";
        // 方案1  非常关键的是 填充模板的输入流！！！
        ExcelWriter excelWriter = EasyExcel.write(fileName).withTemplate(template).build();
        for (int i = 0; i < sheetNo; i++) {
            if (i == 0) {
                WriteSheet writeSheet = EasyExcel.writerSheet(0).build();
                excelWriter.fill(tables, writeSheet);
                continue;
            }
            // 从第二个sheet 填充模板数据
            int tableIndex = i - 1;
            tableName = tables.get(tableIndex).getTableName();
            // 查询单表的相关数据
            List<Columns> columns = columnsMapper.selectBySchema("shop", tableName);
            List<TableConstraintsAndColumn> constraintsAndColumns = tableConstraintsMapper.selectByTableAndSchema("shop", tableName);
            WriteSheet writeSheet = EasyExcel.writerSheet(i).build();  // i是必须的，因为跳过一个模板
            // forceNewRow 代表在写入list的时候不管list下面有没有空行 都会创建一行
            FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
            // 如果有多个list 模板上必须有{前缀.} 这里的前缀就是 data1，然后多个list必须用 FillWrapper包裹
            excelWriter.fill(tables.get(tableIndex), writeSheet);
            excelWriter.fill(new FillWrapper("data1", columns), writeSheet);
            excelWriter.fill(new FillWrapper("data2", constraintsAndColumns), fillConfig, writeSheet);
        }
        excelWriter.finish();
        return 1;
    }

    /**
     * 生成表结构的填充模板
     * @return 表模板的流，ee使用
     */
    public InputStream createTemplate(List<Tables> tables) throws IOException {
        // excel 模板
        String templateFileName = "D:\\templateForIndex.xlsx";
        File file = new File(templateFileName);
        FileInputStream fileInputStream = new FileInputStream(file);
        ByteArrayOutputStream bos = new ByteArrayOutputStream();

        // 准备填充到模板的数据
        int sheetNo = tables.size() + 1;
        // 源模板只有一个sheet， 准备复制模板，动态生成sheet
        XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
        CreationHelper creationHelper = workbook.getCreationHelper();
        //cell style for hyperlinks
        CellStyle hlink_style = workbook.createCellStyle();
        Font hlink_font = workbook.createFont();
        hlink_font.setUnderline(Font.U_SINGLE);
        hlink_font.setColor(IndexedColors.BLUE.getIndex());
        hlink_style.setFont(hlink_font);

        // 设置模板的第一个名称
        workbook.setSheetName(1, tables.get(0).getTableName());
        // 复制模板，得到i个sheet 总共1 + 56
        for (int i = 2; i < sheetNo; i++) {
            int temp = i - 1;
            workbook.cloneSheet(1, tables.get(temp).getTableName());
        }
        // 获得超链接的sheet
        XSSFSheet sheet = workbook.getSheetAt(0);
        // 设置超链接
        for (int i = 1; i < sheetNo; i++) {
            XSSFCell cell = null;
            // 定位目标单元格
            if (i == 1) {
                cell = sheet.getRow(i).getCell(1);
            } else {
                cell = sheet.createRow(i).createCell(1);
            }
            // cell为空终止
            if (cell == null) {
                System.out.println("创建目标单元格失败");
            }
            // 表格的index
            int temp = i - 1;
            // 目标的sheet
            String sheetName = tables.get(temp).getTableName();
            Hyperlink hyperlink = creationHelper.createHyperlink(HyperlinkType.DOCUMENT);
            hyperlink.setAddress("'" + sheetName + "'!A1");
            cell.setHyperlink(hyperlink);
        }

        // 复制到的模板写入流
        workbook.write(bos);
        byte[] bArray = bos.toByteArray();
        InputStream is = new ByteArrayInputStream(bArray);
        bos.flush();
        bos.close();
        return is;
    }

}
